﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports extranet.BE
Imports System.Text
Imports MySql.Data.MySqlClient

Public Class NoticiaDAO
  Private objBD As New BancoDeDados
  Public objQuery As New BEQuery

  Private _conexao As MySqlConnection = Nothing

  Public Sub New()
    _conexao = BancoDeDados.ConnectionInstance()
  End Sub

  Public Function listar() As List(Of Noticia)
    Dim noticias As List(Of Noticia) = New List(Of Noticia)
    Dim noticia As Noticia
    Dim postador As Postador
    Dim consultaSql As String = "SELECT en.id, en.titulo, en.corpo, en.data, en.ativo, ep.id, ep.nome, en.nome_imagem " & _
                                "FROM ext_noticias en inner join ext_postadores ep on en.postadorId=ep.id " & _
                                "ORDER BY en.data desc;"
    Try
      Using command As New MySqlCommand(consultaSql, BancoDeDados.ConnectionInstance())
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            noticia = New Noticia()
            postador = New Postador
            noticia.Id = reader(0)
            noticia.Titulo = reader(1)
            noticia.Corpo = reader(2)
            noticia.DataPostagem = reader(3)
            noticia.Ativo = reader(4)
            noticia.NomeImagem = reader(7).ToString()
            postador.Id = reader(5)
            postador.Nome = reader(6)
            noticia.Postador = postador
            noticias.Add(noticia)
          End While
        End Using

      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiasDAO.listar(): " & ex.Message)
    End Try
    Return noticias
  End Function

  Public Function selecionar(ByVal noticia As Noticia) As Noticia
    Dim postador As Postador
    Dim consultaSql As String = "SELECT en.id, en.titulo, en.corpo, en.data, en.ativo, ep.id, ep.nome, en.nome_imagem " & _
                                "FROM ext_noticias en inner join ext_postadores ep on en.postadorId=ep.id " & _
                                "WHERE en.id = @noticiaId ORDER BY en.data desc;"
    Try
      Using command As New MySqlCommand(consultaSql, BancoDeDados.ConnectionInstance())
        command.Parameters.AddWithValue("@noticiaId", noticia.Id)

        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            noticia = New Noticia
            postador = New Postador
            noticia.Id = reader.GetInt32(0)
            noticia.Titulo = reader.GetString(1)
            noticia.Corpo = reader.GetString(2)
            noticia.DataPostagem = reader.GetDateTime(3)
            noticia.Ativo = reader.GetInt32(4)
            noticia.NomeImagem = reader(7).ToString()
            postador.Id = reader.GetInt32(5)
            postador.Nome = reader.GetString(6)
            noticia.Postador = postador
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiasDAO.selecionar(): " & ex.Message)
    End Try
    Return noticia
  End Function

  Public Function selecionarImagem(ByVal noticia As Noticia) As Byte()
    Dim byteImagem() As Byte = Nothing
    Dim consultaSql As String = "SELECT imagem FROM ext_noticias WHERE id=" & noticia.Id
    Try
      Using command As New MySqlCommand(consultaSql, BancoDeDados.ConnectionInstance())
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            If Not Convert.IsDBNull(reader(0)) Then
              byteImagem = reader(0)
            End If
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiasDAO.selecionarImagem(): " & ex.Message)
    End Try
    Return byteImagem
  End Function

  Public Sub inserir(ByVal noticia As Noticia)
    Try
      noticia.Titulo = objBD.duplicaAspasSimples(noticia.Titulo)
      noticia.Corpo = objBD.duplicaAspasSimples(noticia.Corpo)
      'objBD.duplicaAspasSimples(noticia, GetType(Noticia))
      Dim strSQL = "INSERT INTO ext_noticias (postadorId, titulo, corpo, data, ativo) " & _
                   "VALUES ('" & noticia.Postador.Id & "', '" & _
                                 noticia.Titulo & "', '" & _
                                 noticia.Corpo & "', '" & _
                                 noticia.DataPostagem.ToString("yyyy/MM/dd HH:mm") & "', '" & _
                                 noticia.Ativo & "');" & _
                   "SELECT LAST_INSERT_ID();"
      objBD.InicializarConexao()
      objBD.InicializarTransacao()
      Try
        Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
        noticia.Id = objComando.ExecuteScalar()
        If noticia.NomeImagem.Length > 0 Then
          Dim nPos As Integer = noticia.NomeImagem.LastIndexOf(".")
          If nPos > 0 Then
            noticia.NomeImagem = "noticia" & noticia.Id & noticia.NomeImagem.Substring(nPos)
          Else
            noticia.NomeImagem = "noticia" & noticia.Id
          End If
          strSQL = "Update ext_noticias SET nome_imagem='" & noticia.NomeImagem & "' WHERE id=" & noticia.Id
          objComando = New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
          objComando.ExecuteNonQuery()
        End If
        objBD.Commit()
      Catch ex As ApplicationException
        objBD.Rollback()
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        objBD.FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiaDAO.inserir(): " & ex.Message)
    End Try
  End Sub

  Public Sub atualizar(ByVal noticia As Noticia)
    Try
      noticia.Titulo = objBD.duplicaAspasSimples(noticia.Titulo)
      noticia.Corpo = objBD.duplicaAspasSimples(noticia.Corpo)
      Dim strSQL = "UPDATE ext_noticias SET postadorId='" & noticia.Postador.Id & "', " & _
                                           "titulo='" & noticia.Titulo & "', " & _
                                           "corpo='" & noticia.Corpo & "', " & _
                                           "data='" & noticia.DataPostagem.ToString("yyyy/MM/dd HH:mm") & "', " & _
                                           "ativo='" & noticia.Ativo & "' " & _
                   "WHERE id=" & noticia.Id
      objBD.InicializarConexao()
      objBD.InicializarTransacao()
      Try
        Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
        objComando.ExecuteNonQuery()
        If noticia.NomeImagem.Length > 0 Then
          Dim nPos As Integer = noticia.NomeImagem.LastIndexOf(".")
          If nPos > 0 Then
            noticia.NomeImagem = "noticia" & noticia.Id & noticia.NomeImagem.Substring(nPos)
          Else
            noticia.NomeImagem = "noticia" & noticia.Id
          End If
          strSQL = "Update ext_noticias SET nome_imagem='" & noticia.NomeImagem & "' WHERE id=" & noticia.Id
          objComando = New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
          objComando.ExecuteNonQuery()
        End If
        objBD.Commit()
      Catch ex As ApplicationException
        objBD.Rollback()
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        objBD.FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiaDAO.inserir(): " & ex.Message)
    End Try
  End Sub

  Public Sub deletar(ByVal noticia As Noticia)
    Dim consultaSql As String = "delete from ext_noticias where id = @noticiaId"
    Dim registrosAtualizados As Integer
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        command.Parameters.AddWithValue("@noticiaId", noticia.Id)
        registrosAtualizados = command.ExecuteNonQuery()
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiasDAO.deletar(): " & ex.Message)
    End Try
  End Sub

  Public Sub desativar(ByVal noticia As Noticia)

    Dim consultaSql As String = "update ext_noticias set ativo = 0 where id = @noticiaId"
    Dim registrosAtualizados As Integer
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        command.Parameters.AddWithValue("@noticiaId", noticia.Id)
        registrosAtualizados = command.ExecuteNonQuery()
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiasDAO.desativar(): " & ex.Message)
    End Try
  End Sub

  Public Function PesquisarSimples(ByVal strFiltro As String) As List(Of Noticia)
    Dim noticias As List(Of Noticia) = New List(Of Noticia)
    Dim noticia As Noticia
    Dim consultaSql As String = "SELECT id,postadorId,titulo,corpo,nome_imagem,data,ativo FROM ext_noticias " & _
            " WHERE titulo LIKE '%" & strFiltro & "%' OR corpo LIKE '%" & strFiltro & "%' order by data desc"
    Try
      Using command As New MySqlCommand(consultaSql, _conexao)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            noticia = New Noticia()
            noticia.Id = reader(0)
            noticia.Titulo = reader(2)
            noticia.Corpo = reader(3)
            noticia.NomeImagem = reader(4).ToString()
            noticia.DataPostagem = reader(5)
            noticia.Ativo = reader(6)
            noticias.Add(noticia)
          End While
        End Using

      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiasDAO.listar(): " & ex.Message)
    End Try
    Return noticias
  End Function

  Public Function PesquisarAvancada(ByVal txtBuscaExpressao As String, ByVal txtBuscaQualquer As String, ByVal txtBuscaSem As String, ByVal txtBuscaTodas As String) As List(Of Noticia)
    Dim noticias As List(Of Noticia) = New List(Of Noticia)
    Dim noticia As Noticia
    Dim consultaSql As New StringBuilder()
    Dim boolHouvePrimeiroFiltro As Boolean = False

    consultaSql.Append("SELECT id,postadorId,titulo,corpo,nome_imagem,data,ativo FROM ext_noticias WHERE")

    If txtBuscaExpressao <> "" Then
      If boolHouvePrimeiroFiltro = False Then
        boolHouvePrimeiroFiltro = True
      Else
        consultaSql.Append(" AND ")
      End If
      consultaSql.Append(" corpo LIKE '%" & txtBuscaExpressao & "%'")
    End If

    If txtBuscaQualquer <> "" Then
      If boolHouvePrimeiroFiltro = False Then
        boolHouvePrimeiroFiltro = True
      Else
        consultaSql.Append(" AND ")
      End If
      Dim lstQualquer() As String = txtBuscaQualquer.Split(" ")
      For i As Integer = 0 To lstQualquer.Length - 1
        If i <> 0 Then
          consultaSql.Append(" OR ")
        End If
        consultaSql.Append(" corpo LIKE '%" & lstQualquer(i) & "%'")
      Next
    End If

    If txtBuscaSem <> "" Then
      If boolHouvePrimeiroFiltro = False Then
        boolHouvePrimeiroFiltro = True
      Else
        consultaSql.Append(" AND ")
      End If
      Dim lstSem() As String = txtBuscaSem.Split(" ")
      For i As Integer = 0 To lstSem.Length - 1
        If i <> 0 Then
          consultaSql.Append(" AND ")
        End If
        consultaSql.Append(" corpo NOT LIKE '%" & lstSem(i) & "%'")
      Next
    End If

    If txtBuscaTodas <> "" Then
      If boolHouvePrimeiroFiltro = False Then
        boolHouvePrimeiroFiltro = True
      Else
        consultaSql.Append(" AND ")
      End If
      Dim lstTodas() As String = txtBuscaTodas.Split(" ")
      For i As Integer = 0 To lstTodas.Length - 1
        If i <> 0 Then
          consultaSql.Append(" AND ")
        End If
        consultaSql.Append(" corpo LIKE '%" & lstTodas(i) & "%'")
      Next
    End If

    Try
      Using command As New MySqlCommand(consultaSql.ToString(), _conexao)
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            noticia = New Noticia()
            noticia.Id = reader(0)
            noticia.Titulo = reader(2)
            noticia.Corpo = reader(3)
            noticia.NomeImagem = reader(4).ToString()
            noticia.DataPostagem = reader(5)
            noticia.Ativo = reader(6)
            noticias.Add(noticia)
          End While
        End Using

      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.NoticiasDAO.listar(): " & ex.Message)
    End Try
    Return noticias
  End Function

End Class
